IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pGetTotalReturnPrice'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pGetTotalReturnPrice
GO

CREATE PROC dbo.pGetTotalReturnPrice
	@vdatFromDate	DATETIME,
	@vdatToDate		DATETIME
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pGetTotalReturnPrice'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT
	
	
	DECLARE @intTotalReturn INT
	DECLARE @intTotalReturnCalculated	INT
/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/
	

/*_______________________________ MAIN _________________________________*/
	SET @intTotalReturn = 0
	SET @intTotalReturnCalculated = 0
	
	-- tinh tong gia tri tra hang
	-- ngay tra thuoc khoang thoi gian thong ke va ngay ban ko thuoc thoi gian thong ke
	SELECT @intTotalReturn = SUM(ISNULL(ReturnProductDetail.Amount * ReturnProductDetail.SalePrice, 0))
	FROM ReturnProductDetail
	
	INNER JOIN ReturnProduct
		ON ReturnProductDetail.ReturnProductId = ReturnProduct.Id
		
	WHERE ((DATEDIFF(DAY, @vdatFromDate, ReturnProduct.ReturnDate) >= 0)
		AND (DATEDIFF(DAY, ReturnProduct.ReturnDate, @vdatToDate) >= 0))
		
		AND ((DATEDIFF(DAY, @vdatFromDate, ReturnProductDetail.SaleTime) < 0)
			OR (DATEDIFF(DAY, ReturnProductDetail.SaleTime, @vdatToDate) < 0))

	-- tinh tong gia tri tra hang da khau tru
	-- ngay ban thuoc thoi gian thong ke nhung ngay tra khong thuoc thoi gian thong ke
	SELECT @intTotalReturnCalculated = SUM(ISNULL(ReturnProductDetail.Amount * ReturnProductDetail.SalePrice, 0))
	FROM ReturnProductDetail
	
	INNER JOIN ReturnProduct
		ON ReturnProductDetail.ReturnProductId = ReturnProduct.Id
		
	WHERE ((DATEDIFF(DAY, @vdatFromDate, ReturnProduct.ReturnDate) < 0)
		OR (DATEDIFF(DAY, ReturnProduct.ReturnDate, @vdatToDate) < 0))
		
		AND ((DATEDIFF(DAY, @vdatFromDate, ReturnProductDetail.SaleTime) >= 0)
			AND (DATEDIFF(DAY, ReturnProductDetail.SaleTime, @vdatToDate) >= 0))
			
	SELECT 
		TotalReturn=@intTotalReturn,
		TotalReturnCalculated=@intTotalReturnCalculated
			
  -- Return value 0 indicates success
  SET @intReturn = 0

lblExit:

  RETURN @intReturn

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


